package com.hp.ams.ongoing.netatendimento.infrastructure.filehandle;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {

	 public static void main(String... args)
	  {
	    // name of Excel file you want to read.
	    String fileName = "C:/chamado.xlsx";
	    
	    // Read an Excel File and Store in an ArrayList
	    ArrayList<ArrayList<XSSFCell>> dataHolder = readExcelFile(fileName);
	    
	    // Print the data read
	    printCellDataToConsole(dataHolder);
	  }

	  public static ArrayList<ArrayList<XSSFCell>> readExcelFile(String fileName)
	  {
	    /**
	     * --Define a List --Holds List Of Cells
	     */
	    ArrayList<ArrayList<XSSFCell>> cellListHolder =
	        new ArrayList<ArrayList<XSSFCell>>();

	    try
	    {
	      /* Creating Input Stream */
	      FileInputStream myInput = new FileInputStream(fileName);

	      /* Create a workbook using Stream */
	      XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);

	      /* Get the first sheet from workbook */
	      XSSFSheet mySheet = myWorkBook.getSheetAt(0);

	      /* iterate through the cells. */
	      
	      Iterator rowIter = mySheet.rowIterator();

	      while (rowIter.hasNext())
	      {
	        XSSFRow myRow = (XSSFRow) rowIter.next();
	        Iterator cellIter = myRow.cellIterator();
	        ArrayList<XSSFCell> cellStoreList = new ArrayList<XSSFCell>();
	        while (cellIter.hasNext())
	        {
	          XSSFCell cell = (XSSFCell) cellIter.next();
	          cell.setCellType(Cell.CELL_TYPE_STRING);
	          cellStoreList.add(cell);
	        }
	        if (!(cellStoreList.get(0).toString().equals("")))
	        cellListHolder.add(cellStoreList);
	      }
	    }
	    catch (Exception e)
	    {
	      e.printStackTrace();
	    }
	    return cellListHolder;
	  }

	  /**
	   * prints data to console
	   * 
	   * @param dataHolder
	   */
	  public static void printCellDataToConsole(ArrayList<ArrayList<XSSFCell>> dataHolder)
	  {

	    for(ArrayList<XSSFCell> cellList : dataHolder)
	    {
	      for(XSSFCell cell: cellList)
	      {
	        String stringCellValue = cell.toString();
	        System.out.print(stringCellValue +"\t");
	      }
	      System.out.println();
	    }

	  }
	}

	

